from datetime import datetime
from datebase import Base, engine
from sqlalchemy import DateTime, ForeignKeyConstraint, Index, Integer, PrimaryKeyConstraint, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from pydantic import BaseModel, EmailStr
from typing import List, Optional


# 数据库模型
class Users(Base):
    __tablename__ = 'users'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='users_pkey'),
        Index('ix_test_one_users_email', 'email', unique=True),
        Index('ix_test_one_users_id', 'id'),
        Index('ix_test_one_users_username', 'username', unique=True),
        {'schema': 'test_one'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[Optional[str]] = mapped_column(String)
    email: Mapped[Optional[str]] = mapped_column(String)
    created_at: Mapped[Optional[datetime]] = mapped_column(DateTime)

    hobbies: Mapped[List['Hobbies']] = relationship('Hobbies', back_populates='user', lazy=False,
                                                    cascade="all, delete-orphan")


class Hobbies(Base):
    __tablename__ = 'hobbies'
    __table_args__ = (
        ForeignKeyConstraint(['user_id'], ['test_one.users.id'], name='hobbies_user_id_fkey'),
        PrimaryKeyConstraint('id', name='hobbies_pkey'),
        Index('ix_test_one_hobbies_id', 'id'),
        {'schema': 'test_one'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    hobname: Mapped[Optional[str]] = mapped_column(String)
    hobtime: Mapped[Optional[int]] = mapped_column(Integer)
    user_id: Mapped[Optional[int]] = mapped_column(Integer)

    user: Mapped[Optional['Users']] = relationship('Users', back_populates='hobbies')


# 格式校验模型
# 爱好模型
class HobbyCreate(BaseModel):
    hobname: str
    hobtime: int


# 用户创建请求模型
class UserCreate(BaseModel):
    userId: Optional[int] = None
    username: Optional[str] = None
    email: Optional[str] = None  # EmailStr  # 自动验证邮箱格式
    hobby: Optional[List[HobbyCreate]] = None  # 嵌套列表


# 用户响应模型（包含 ID）
class UserResponse(UserCreate):
    id: int

    class Config:
        from_attributes = True  # 允许从 ORM 对象转换


if __name__ == '__main__':
    # 确保模式存在
    from sqlalchemy import text

    with engine.connect() as conn:
        conn.execute(text("CREATE SCHEMA IF NOT EXISTS test_one"))
        conn.commit()

    # 创建表
    Base.metadata.create_all(bind=engine)
